package com.iris.live.services.data.repositories.impl;

import com.google.common.collect.Maps;
import com.iris.live.services.common.config.Constants;
import com.iris.live.services.common.enumpack.RoleAuthorityEnum;
import com.iris.live.services.data.repositories.CommonSearchRepository;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Map;


/**
 * Created by wx on 2017/12/27.
 */
@Repository
@Transactional
public class CommonSearchRepositoryImpl extends BaseRepository implements CommonSearchRepository {

    /**
     * 获取用户名所属大区LIST
     *
     * @param frUserName
     * @param frAuthority （RD:大区）
     * @return
     */
    @Override
    public List getAreaLists(String frUserName, String frAuthority, String sensitivity) {

        Map<String, Object> params = Maps.newHashMap();
        StringBuffer sb = new StringBuffer();
        //集团 取全部
        if (RoleAuthorityEnum.Group.getvalue().equals(frAuthority)) {
            sb.append(" 	SELECT 'all' 大区_EN ");
            sb.append(" UNION ");
            sb.append(" 	SELECT DISTINCT 大区_EN FROM vw_group_report_area ");
            sb.append(" 	WHERE 大区_EN  <> '' ");
            sb.append(" 	AND 大区_EN IS NOT NULL ");
        }else { //非集团
            //前端页面如果敏感，则只显示该用户自己所属的大区
            if(Constants.SENSITIVITY_YES == Integer.parseInt(sensitivity)) {
                sb.append(" SELECT ");
                sb.append(" 	DISTINCT 大区_EN FROM vw_group_report_user ");
                sb.append(" WHERE ");
                sb.append(" 	大区_EN  <> '' ");
                sb.append(" 	AND 大区_EN IS NOT NULL ");
                sb.append(" 	AND 用户名 =:frUserName ");
                params.put("frUserName", frUserName);
            }else {
                if (RoleAuthorityEnum.Area.getvalue().equals(frAuthority)) {
                    sb.append(" SELECT ");
                    sb.append(" 	'all' 大区_EN ");
                    sb.append(" UNION ");
                    sb.append(" 	SELECT DISTINCT 大区_EN FROM vw_group_report_user ");
                    sb.append(" WHERE ");
                    sb.append(" 	大区_EN  <> '' ");
                    sb.append(" 	AND 大区_EN IS NOT NULL ");
                    sb.append(" 	AND 用户名 =:frUserName ");
                    params.put("frUserName", frUserName);
                } else {
                    sb.append(" 	SELECT 'all' 大区_EN ");
                    sb.append(" UNION ");
                    sb.append(" 	SELECT DISTINCT 大区_EN FROM vw_group_report_area ");
                    sb.append(" 	WHERE 大区_EN  <> '' ");
                    sb.append(" 	AND 大区_EN IS NOT NULL ");
                }
            }
        }

        return getList(sb.toString(), params);
    }

    /**
     * 获取用户名所属PMA LIST
     *
     * @param frUserName
     * @param frAuthority （CTA:PMA）
     * @return
     */
    @Override
    public List getPmaLists(String frUserName, String frAuthority, String selRegionCode, String sensitivity) {

        Map<String, Object> params = Maps.newHashMap();
        StringBuffer sb = new StringBuffer();
        if (RoleAuthorityEnum.Group.getvalue().equals(frAuthority)) {
            sb.append(" SELECT ");
            sb.append(" 	大区_EN,CTA_EN FROM ");
            sb.append(" 	(SELECT DISTINCT 大区_EN,CTA_EN FROM vw_group_report_dealer ");
            sb.append(" UNION ");
            sb.append(" 	SELECT 'all' 大区_EN,'all' CTA_EN FROM vw_group_report_dealer ");
            sb.append(" UNION ");
            sb.append(" 	SELECT DISTINCT 大区_EN,'all' CTA_EN FROM vw_group_report_dealer) a ");
            sb.append(" WHERE ");
            sb.append(" 	CTA_EN  <> '' ");
            sb.append(" 	AND CTA_EN IS NOT NULL ");
            sb.append(" 	AND 大区_EN = :selRegionCode   ");
            params.put("selRegionCode", selRegionCode);

        }else {
            //当前登录人是PMA
            if (RoleAuthorityEnum.PMA.getvalue().equals(frAuthority)) {
                //敏感
                if(Constants.SENSITIVITY_YES == Integer.parseInt(sensitivity)) {
                    sb.append(" SELECT ");
                    sb.append(" 	DISTINCT 大区_EN  AS CTA_EN FROM vw_group_report_user ");
                    sb.append(" WHERE ");
                    sb.append(" 	大区_EN  <> '' ");
                    sb.append(" 	AND 大区_EN IS NOT NULL ");
                    sb.append(" 	AND 用户名 =:frUserName ");
                    params.put("frUserName", frUserName);
                }else{
                    //非敏感
                    sb.append(" SELECT ");
                    sb.append(" 	大区_EN AS CTA_EN FROM ");
                    sb.append(" 	(SELECT 'all' 大区_EN ");
                    sb.append(" UNION ");
                    sb.append(" 	SELECT DISTINCT 大区_EN  FROM vw_group_report_user ");
                    sb.append(" WHERE ");
                    sb.append(" 	大区_EN  <> '' ");
                    sb.append(" 	AND 大区_EN IS NOT NULL ");
                    sb.append(" 	AND 用户名 =:frUserName) A ");
                    params.put("frUserName", frUserName);
                }
            }else{
                //当前登录人不是PMA
                sb.append(" SELECT ");
                sb.append(" 	大区_EN,CTA_EN FROM ");
                sb.append(" 	(SELECT DISTINCT 大区_EN,CTA_EN FROM vw_group_report_dealer ");
                sb.append(" UNION ");
                sb.append(" 	SELECT 'all' 大区_EN,'all' CTA_EN FROM vw_group_report_dealer ");
                sb.append(" UNION ");
                sb.append(" 	SELECT DISTINCT 大区_EN,'all' CTA_EN FROM vw_group_report_dealer) a ");
                sb.append(" WHERE ");
                sb.append(" 	CTA_EN  <> '' ");
                sb.append(" 	AND CTA_EN IS NOT NULL ");
                sb.append(" 	AND 大区_EN = :selRegionCode   ");
                params.put("selRegionCode", selRegionCode);
            }
        }
        return getList(sb.toString(), params);
    }

    /**
     * 获取经销商LIST (针对 LSH,LXH)
     *
     * @param selRegionCode
     * @param selPMACode
     * @return
     */
    @Override
    public List getDealerLists(String selRegionCode, String selPMACode) {
        Map<String, Object> params = Maps.newHashMap();
        String sql =
                " SELECT 'all' 许可_BMBS,'all' showValue " +
                " UNION " +
                " SELECT DISTINCT 许可_BMBS,许可_LSH showValue FROM lsh经销商表 " +
                " WHERE  1=1 " +
                " AND 大区_EN IS NOT NULL " +
                " AND 大区_EN = :selRegionCode ";
        params.put("selRegionCode", selRegionCode);
        //大区下的经销商  other
        if(RoleAuthorityEnum.Dealer.getvalue().equals(selPMACode)) {
            sql += " AND CTA_EN = '' ";
        }else {
            sql += " AND CTA_EN = :selPMACode ";
            params.put("selPMACode", selPMACode);
        }
        sql += " ORDER BY 许可_BMBS <> 'all' ";
        return getList(sql, params);
    }

    /**
     * 获取经销商LIST (针对 DCH,LVD)
     *
     * @param selRegionCode
     * @return
     */
    @Override
    public List getDealerLists2(String selRegionCode) {
        Map<String, Object> params = Maps.newHashMap();
        String sql =
                " SELECT 'all' 许可_BMBS, '全部' showValue " +
                " UNION" +
                " SELECT DISTINCT " +
                "  d.`许可_BMBS`, " +
                "  concat(d.`许可_BMBS`,' ',d.经销商名) AS showValue " +
                " FROM  `用户` b,vw_group_report_dealer d " +
                " WHERE 1 = 1 " +
                " AND d.`许可_BMBS` = b.`许可` " +
                " AND d.`许可_BMBS` <> '' " +
                " AND d.`许可_BMBS` IS NOT NULL " +
                " AND d.`大区_EN` = :selRegionCode " +
                " AND b.`用户名` <> 'Admin' " +
                " AND b.`角色` NOT IN ( " +
                "     SELECT t.roleName FROM lsh_group_role_map t " +
                " ) " +
                " ORDER BY 许可_BMBS <> 'all' ";
        params.put("selRegionCode", selRegionCode);
        return getList(sql, params);
    }

    @Override
    public List getActivityTypes() {
        String sql =
                " SELECT 'all' 活动类型 \n" +
                        " UNION \n" +
                        " (SELECT DISTINCT 活动类型 FROM `vw_group_report_activity_list`  ORDER BY 活动类型)";
        return this.getList(sql);
    }

    @Override
    public List getLeadingAndLaunches() {
        String sql =
                " SELECT 'all' 主导与发起  \n" +
                        " UNION\n" +
                        " (SELECT DISTINCT 主导与发起 FROM `vw_group_report_activity_list` ORDER BY 主导与发起)";
        return this.getList(sql);
    }

    @Override
    public List getCarSeries(String selBrand) {
        String sql =
                " SELECT 'all' 车系    \n" +
                        " UNION \n" +
                        " (SELECT DISTINCT 级别 as 车系 FROM lsh车型  WHERE 1=1 " +
                        " AND 品牌 <> '' " +
                        " AND 级别 <> '' " +
                        " AND 品牌 = :brand) ";
        Map params = Maps.newHashMap();
        params.put("brand", selBrand);
        return this.getList(sql, params);
    }

    @Override
    public List getCarModes(String selBrand, String selMotorcycleType) {
        String sql =
                " SELECT 'all' 车型   \n" +
                        " UNION \n" +
                        " (SELECT DISTINCT 车型 FROM lsh车型 WHERE 1=1 " +
                        "   AND 车型 <> '' " +
                        "   AND 车型 is not null " +
                        "   AND 品牌 = :brand " +
                        "   AND 级别 = :motorcycleType) " ;

        Map params = Maps.newHashMap();
        params.put("brand", selBrand);
        params.put("motorcycleType",selMotorcycleType);
        return this.getList(sql, params);
    }

    @Override
    public List getFinanceLoanTypes() {
        String sql =
                " SELECT 'all'  贷款类型 " +
                        " UNION " +
                        " (SELECT 贷款类型 FROM vw_group_report_fin_loantype ORDER BY 贷款类型)";
        List list = this.getList(sql);

        return list;
    }

    @Override
    public List getBrands(String sql) {

        List list = this.getList(sql);

        return list;
    }

    @Override
    public List getBrandTotals(String sql) {
        List list = this.getList(sql);

        return list;
    }

    @Override
    public List getChannels() {
        String sql =
                " SELECT 'all' channel  \n" +
                        " UNION  \n" +
                        " SELECT channel FROM t_channel_list a WHERE a.isFirst=1 ORDER BY channel <> 'all' ";
        List list = this.getList(sql);

        return list;
    }

    @Override
    public List getFinancialInstitutions() {
        String sql =
                " SELECT 'all'  金融机构 " +
                        " UNION " +
                        " (SELECT 金融机构 FROM vw_group_report_fin_agency ORDER BY 金融机构)";
        return this.getList(sql);
    }

    @Override
    public List getResearchTypes() {
        String sql = "SELECT distinct stage,stagename FROM `vw_group_report_crm_version_info`";
        return this.getList(sql);
    }

    @Override
    public List getQuestionVersion(String stage) {
        String sql = "SELECT version FROM `vw_group_report_crm_version_info`  WHERE stage = :stage";
        Map params = Maps.newHashMap();
        params.put("stage", stage);
        return this.getList(sql, params);
    }

    @Override
    public List getSalesConsultants(String userName) {
        Map params = Maps.newHashMap();
        String sql =
                " SELECT '全部' 人员姓名,'all' 用户名 \n" +
                " UNION all\n" +
                " (SELECT 人员姓名,用户名 FROM `用户` \n" +
                " where 1=1\n" +
                " and 角色 = '销售顾问' \n" +
                " and 删除状态 <> '已删除' \n" +
                " and 许可 = (SELECT 许可 FROM 用户 WHERE 用户名 = :userName)  \n" +
                " order by  CONVERT(人员姓名 USING gbk) asc )\n";
        params.put("userName",userName);

        return this.getList(sql,params);
    }

    @Override
    public List getPermitByUserName(String userName) {
        String sql = "SELECT 许可 FROM 用户 WHERE 用户名=:userName ";
        Map params = Maps.newHashMap();
        params.put("userName", userName);
        return this.getList(sql, params);
    }
}
